# import pymysql
# from DBUtils.PooledDB import PooledDB
# import traceback
#
# POOL = PooledDB(
#     creator=pymysql,  # 使用连接数据库的模块
#     maxconnections=20,  # 连接池允许的最大连接数，0和None表示不限制连接数
#     mincached=3,  # 初始化时，连接池中至少创建的空闲的链接，0表示不创建
#     maxcached=1,  # 连接池中最多闲置的连接，0和None不限制
#     maxshared=0,
#     # 连接池中最多共享的连接数量，0和None表示全部共享。PS: 无用，因为pymysql和MySQLdb等模块的 threadsafety都为1，所有值无论设置为多少，_maxcached永远为0，所以永远是所有连接都共享。
#     blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
#     maxusage=None,  # 一个连接最多被重复使用的次数，None表示无限制
#     setsession=[],  # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
#     ping=0,
#     # ping MySQL服务端，检查是否服务可用，如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always
#     host="192.168.31.143",  # 数据库地址
#     port=3306,  # 数据库端口
#     user="root",  # 用户名
#     password="qw123666!@#",  # 密码
#     database="ppp_test"  # 数据库名称
# )
#
#
# class DataBaseHandle(object):
#     """定义MYSQL数据库操作类"""
#
#     def execSql(self, sql):
#         """插入数据"""
#         conn = POOL.connection()
#         try:
#             with conn.cursor() as curs:
#                 curs.execute(sql)
#         except Exception as e:
#             conn.rollback()
#             raise e
#         finally:
#             conn.commit()
#             conn.close()
#
#     def insertListDB(self, table, dataList):
#         """批量插入列表数据
#         Params:
#             table:插入数据的表名称
#             dataList:数据列表 [{key:value,}{key:value,},...]
#         """
#
#         conn = POOL.connection()
#         try:
#
#             cols = ', '.join('`{}`'.format(k) for k in dataList[0].keys())
#             val_cols = ', '.join('%({})s'.format(k) for k in dataList[0].keys())
#             sql = 'INSERT INTO {}(%s) values(%s)'.format(table)
#             res_sql = sql % (cols, val_cols)
#
#             with conn.cursor() as curs:
#                 return curs.executemany(res_sql, dataList)  # 将字典列表传入
#         except Exception as e:
#             conn.rollback()
#             raise e
#         finally:
#             conn.commit()
#             conn.close()
#
#     def replaceListDB(self, table, dataList):
#         """批量插入列表数据
#         Params:
#             table:插入数据的表名称
#             dataList:数据列表 [{key:value,}{key:value,},...]
#         """
#
#         conn = POOL.connection()
#         try:
#
#             cols = ', '.join('`{}`'.format(k) for k in dataList[0].keys())
#             val_cols = ', '.join('%({})s'.format(k) for k in dataList[0].keys())
#             sql = 'replace  INTO {}(%s) values(%s)'.format(table)
#             res_sql = sql % (cols, val_cols)
#
#             with conn.cursor() as curs:
#                 return curs.executemany(res_sql, dataList)  # 将字典列表传入
#         except Exception as e:
#             conn.rollback()
#             raise e
#         finally:
#             conn.commit()
#             conn.close()
#
#     def selectDB(self, sql):
#         """查询数据"""
#
#         conn = POOL.connection()
#         try:
#             with conn.cursor(cursor=pymysql.cursors.DictCursor) as curs:
#                 curs.execute(sql)
#                 return curs.fetchall()
#         except Exception as e:
#             conn.rollback()
#             raise e
#         finally:
#             conn.commit()
#             conn.close()
#
#     def selectDBdict(self, sql):
#         return self.selectDB(sql)
#
#     def selectDBlist(self, sql):
#         """查询数据"""
#
#         conn = POOL.connection()
#         try:
#             with conn.cursor() as curs:
#                 curs.execute(sql)
#                 resData = curs.fetchall()
#                 return [list(item) for item in resData]
#         except Exception as e:
#             conn.rollback()
#             raise e
#         finally:
#             conn.commit()
#             conn.close()
#
#
# if __name__ == '__main__':
#     pass
